<?php
	require_once('start_session.php');
	require_once('db_connect.php');
	require_once('distance.php');
	
	function getKeywords($survey_id) {
		global $conn;
		$keyword_list = array();
		$query = "select * from keyword_survey where survey_id = ".$survey_id;
		$kw_data = mysqli_query($conn, $query)
			or die("keyword_survey에서 survey id ".$row['id']." 로 가져오는 쿼리 실패");
		while ($kw_row = mysqli_fetch_array($kw_data)) {
			array_push($keyword_list, $kw_row['keyword']);
		}
		return $keyword_list;
	}

	$is_login = isset($_COOKIE['user_id']);
	$user_id = $_COOKIE['user_id'];
	$session_ip = $_SESSION['ip'];
	$session_connect_time = $_SESSION['connect_time'];

	$member_result = mysqli_query($conn, "select * from member where id = '$user_id'");
	$member_row = mysqli_fetch_array($member_result);

	$is_detail = isset($GLOBALS['HTTP_RAW_POST_DATA']) && !empty($GLOBALS['HTTP_RAW_POST_DATA']);
	if ($is_detail) {
		$query_data = json_decode($GLOBALS['HTTP_RAW_POST_DATA']);
		$title = $query_data->{"title"};
		$category = $query_data->{"category"};
		$keyword = $query_data->{"keyword"};
		$is_allow_expire = $query_data->{"is_allow_expire"};
		$location = $query_data->{"location"};
		$startdate = $query_data->{"startdate"};
		$enddate = $query_data->{"enddate"};
		$startime = $query_data->{"starttime"};
		$endtime = $query_data->{"endtime"};
		$basic_conds = $query_data->{"basic_conds"};
	}
	else {
		$location = $_GET['location'];
		$latitude = $_GET['latitude'];
		$longitude = $_GET['longitude'];
		$connect_datetime = $_GET['connect_time'];
		$mem_id = $_GET['mem_id'];
		$keyword = $_GET['keyword'];
	}
	// build query
	// --------------------------------------------------------------------------
	$survey_query = "select * from survey";
	if (!empty($keyword))
		$survey_query .= ", keyword_survey";
	if (isset($basic_conds) && count($basic_conds) > 0) {
		$survey_query .= ", basic_condition";
		$query_where .= " and survey.id = basic_condition.survey_id ";
	}
		
	// title
	if (!empty($title)) {
		$query_where .= " and title like '%$title%' ";
	}
	// category
	if (!empty($category)) {
		$query_where .= " and category = '$category' ";
	}
	// startdate
	if (!empty($startdate)) {
		$query_where .= " and start_date >= '$startdate' ";
	}
		//$startdate = '';//date("Y-m-d H:i:s");
//	if (!empty($startdate)) {
//	}
	// enddate
	if (!empty($enddate)) {
		if (isset($is_allow_expire) && $is_allow_expire == true)
			$enddate = date("Y-m-d H:i:s", max(time(), strtotime($enddate)));
	
		$query_where .= " and end_date <= '$enddate' ";
	}

	// starttime
	if (!empty($starttime)) {
		$query_where .= " and start_time >= '$starttime' ";
	}
	// endtime
	if (!empty($endtime)) {
		$query_where .= " and end_time <= '$endtime' ";
	}
	// basic conditions
	if (isset($basic_conds)) {
		foreach ($basic_conds as $basic_cond) {
			$basic_cond_where .= ' or (age = '.$basic_cond->{"age"}.
													' and gender = '.$basic_cond->{"gender"}.
													' and edulevel = '.$basic_cond->{"edulevel"}.
													(empty($basic_cond->{"addr"}) ? '' : 'and addr = \''.$basic_cond->{"addr"}.'\'').
													') ';
		}
		$basic_cond_where = preg_replace('/^\s*or/', '', $basic_cond_where);
		if (!empty($basic_cond_where))
			$query_where .= " and ($basic_cond_where) ";
	}
	$location_where = '';
	// ap location
	if (!empty($location)) {
		$query = "select * from standard_ap where locatename = '$location'";
		$data = mysqli_query($conn, $query)
			or die("$location 으로 standard_ap에서 값 찾기 쿼리 실패");
		$location_type = "";
		$location_id = null;
		if (mysqli_num_rows($data) > 0) {
			$row = mysqli_fetch_array($data);
			$location_id = $row['ap_id'];
			$location_type = "ap";
		}
		$query = "select * from standard_gps where locatename = '$location'";
		$data = mysqli_query($conn, $query)
			or die("$location 으로 standard_gps에서 값 찾기 쿼리 실패");
		if (mysqli_num_rows($data) > 0) {
			$row = mysqli_fetch_array($data);
			$location_id = $row['gps_id'];
			$location_type = "gps";
		}
		if (!$location_id)
			exit("ERROR: $location 의 위치가 DB에 없음");
		
		if ($is_detail)
			$location_where .= ' and '.($location_type == "ap" ? "ap_id = " : "gps_id = ")."$location_id ";
		else
			$location_where .= ' and ap_id = '.$location_id.' ';
	}
	
	// gps with lat lng
	if (!empty($latitude) && !empty($longitude)) {
		$query = "select * from standard_gps";
		$data = mysqli_query($conn, $query)
			or die("standard_gps 테이블 가져오는 쿼리 실패");
		$gps_where = "";
		while ($row = mysqli_fetch_array($data)) {
			if (distance($latitude, $longitude, $row['latitude'], $row['longitude']) <= $row['radius'])
				$gps_where .= 'or gps_id = '.$row['gps_id'].' ';
		}
		if (!empty($gps_where)) {
			$gps_where = preg_replace('/^s*or/', '', $gps_where);
			$location_where .= ' and ('.$gps_where.')';
		}
	}
	// ap와 gps 모두 널인 것 포함
	$location_where = preg_replace('/^\s*and/', '', $location_where);
	
	if (!empty($location_where)) {
		if ($is_detail)
			$query_where .= $location_where;
		else
			$query_where .= ' and ((ap_id IS NULL AND gps_id IS NULL) or '.$location_where.') ';
	}
	
	// connect_datetime
	if (!empty($connect_datetime)) {
		$connect_timepart = date("H:i:s", strtotime($connect_datetime));
		$query_where .= " and (start_date <= '$connect_datetime' and end_date >= '$connect_datetime') ".
						 " and (start_time <= '$connect_timepart' and end_time >= '$connect_timepart') ";
	}
	// mem id
	if (!empty($mem_id)) {
		$query_where .= " and member_id = '$mem_id'";
	}
	// keyword
	if (!empty($keyword)) {
		$query_where .= " and survey.id = keyword_survey.survey_id and keyword_survey.keyword like '%$keyword%'";
	}
	
	$query_where = preg_replace('/^\s*and/', '', $query_where);
	if (!empty($query_where))
		$survey_query .= ' where '.$query_where;
	// --------------------------------------------------------------------------
	
	// $query = "select * from survey where ".($location_type == "ap" ? "ap_id = " : "gps_id = ")."$location_id ".
						// "and (end_date >= '$connect_datetime') ".
						// "and (start_time <= '$connect_timepart' and end_time >= '$connect_timepart')".
						// (empty($mem_id) ? "" : "and member_id = '$mem_id'");
	$data = mysqli_query($conn, $survey_query)
		or die("query: $survey_query \n설문목록 가져오는 쿼리 실패");
	if (mysqli_num_rows($data) <= 0)
		exit(json_encode(array('survey_list' => array(), 'query' => array($survey_query))));
	
// 'title': string,
// 'category': string,
// 'keyword': string array,
// 'is_once': number(0 or 1),
// 'start_date': date type string,
// 'end_date' date type string,
// 'start_time': time type string,
// 'end_time': time type string,
// 'max_capacity': number,
// 'require_login': number(0 or 1),
// 'is_mid_result' number(0 or 1),
// 'member_id': string,
// 'view_count': number

	$survey_list = array();
	while ($row = mysqli_fetch_array($data)) {
		$keyword_list = getKeywords($row['id']);
		// getting results number
		$query = "select * from result where survey_id = ".$row['id'];
		$rdata = mysqli_query($conn, $query)
			or die('result table에서 survey id'.$row['id'].'로 가져오는 쿼리 실패');
		$numResults = mysqli_num_rows($rdata);
		//

		//1. 위치조건 만족
		// --------------------------------------------------------------------------
		$condition_satisfy_location = 0;
		if($row[ap_id] != NULL)	{ //ap조건이 있는 경우 만족하면 통과
			$query = "select ap_id from standard_ap where locatename = '$location' and ap_id = '$row[ap_id]'";
			//의 결과가 한 튜플 나오면 만족.
			$result = mysqli_query($conn, $query);		
			if(mysqli_num_rows($result) >= 1) {
				$condition_satisfy_location = 1;
			}
		}
		if($row[gps_id] != NULL) { // gps조건이 있는 경우 만족하면 통과
			if (!empty($latitude) && !empty($longitude)) {
				$query = "select * from standard_gps where gps_id = '$row[gps_id]'";
				$result = mysqli_query($conn, $query);
				$result_row = mysqli_fetch_array($result);
				if(distance($latitude, $longitude, $result_row[latitude], $result_row[longitude]) < $result_row[radius]) {
					$condition_satisfy_location = 1;
				}
			}
		}
		if($row[ap_id] == NULL && $row[gps_id] == NULL)	//위치조건이 없으면 접속자에 상관없이 통과
			$condition_satisfy_location = 1;
		// --------------------------------------------------------------------------
		
		//2. 참여가능한 날짜,시간대 만족
		// --------------------------------------------------------------------------
		$condition_satisfy_time = 0;
		
		//우선 현재시간(년월일시간분초)이 start_date(datetime)과 end_date(datetime) 범위에 속하는지 확인
		$query = "select now() from category where now() between '$row[start_date]' and '$row[end_date]'";
		$result1 = mysqli_query($conn, $query);
		$result1_rows = mysqli_num_rows($result1);
		
		//그다음 현재시간의 시간이 시간대가 맞는지 확인
		$query = "select cast(now() as time) from category where cast(now() as time) between '$row[start_time]' and '$row[end_time]'";
		$result2 = mysqli_query($conn, $query);
		$result2_rows = mysqli_num_rows($result2);
		
		if($result1_rows > 0 && $result2_rows > 0)
			$condition_satisfy_time = 1;
		// --------------------------------------------------------------------------
		
		
		//3. 설문의 기본조건이 있을 때, 로그인 되어 있는 회원이고 and 회원정보가 기본조건에 해당될때
		// --------------------------------------------------------------------------
		$condition_satisfy_basiccond = 0;
		//이 설문의 기본조건이 없으면, 바로 만족
		//기본조건이 있는데 회원로그인이 안되어있으면 불만족
		//기본조건이 있고 회원정보로 검색한 내용이 1레코드이상 나오면 만족
		//                                         0레코드이면 안만족

		$query = "select * from basic_condition where survey_id = '$row[id]'";
		$result = mysqli_query($conn, $query);
		$result_rows = mysqli_num_rows($result);
		
		if($result_rows == 0)	{
			//기본조건이 없다.
			$condition_satisfy_basiccond = 1;
		}
		else {
			//기본조건이 있다.
			if($is_login == 0) {
				//로그인이 안되어있으면 실패
				$condition_satisfy_basiccond = 0;
			}
			else {
				//로그인이 되어있으면, 기본조건들중 회원정보와 일치하는게 있는지 확인
				if($member_row[age] < 20) $member_cond_age = 10;
				else if($member_row[age] < 30) $member_cond_age = 20;
				else if($member_row[age] < 40) $member_cond_age = 30;
				else if($member_row[age] < 50) $member_cond_age = 40;
				else $member_cond_age = 50;
				
				$member_cond_edulevel = $member_row[edulevel];
				$member_cond_gender = $member_row[gender];
				$member_cond_addr = $member_row[addr];
				
				//select * from basic_condition where '서울시 어쩌구저쩌구' like CONCAT('%', addr, '%') and addr <> ''
				$query = "select * from basic_condition where survey_id = '$row[id]' and (age = '$member_cond_age' or age = '0') and (edulevel = '$member_cond_edulevel' or edulevel = '') and (gender = '$member_cond_gender' or gender = '0') and (('$member_cond_addr' like CONCAT('%', addr,'%') and addr <> '') or addr = '')";
				// echo $query;
				
				$result = mysqli_query($conn, $query);
				$result_rows = mysqli_num_rows($result);
				if($result_rows >= 1)
					$condition_satisfy_basiccond = 1;
				else
					$condition_satisfy_basiccond = 0;
			}
		}
		
		//4. 1회성 설문인경우 - 아직참여하지 않은 경우
		// --------------------------------------------------------------------------
		$condition_satisfy_isonce = 0;
		//	 무제한 설문인경우 - 오늘 참여하지 않은 경우(answer테이블을 보면 알수 있음)

		if($row[is_once] == 1) { //무제한
			if($is_login == 1) //회원인경우 이 survey에 대해 오늘 참여한 id가 answer에 없으면 통과1임
				$query = "select * from answer where survey_id = '$row[id]' and mem_id = '$user_id' and cast(submit_time as date) = cast(now() as date)";
			else	//비회원(회원도 포함)인 경우 현재의 IP, connect time이 오늘참여한 answer에 없으면 통과1임
				$query = "select * from answer where survey_id = '$row[id]' and ip = '$session_ip' and connect_time = '$session_connect_time' and cast(submit_time as date) = cast(now() as date)";
		}
		else { //1회성 설문
			if($is_login == 1) //회원인경우 이 survey에 대해 지금까지 참여한 id가 answer에 없으면 통과1임
				$query = "select * from answer where survey_id = '$row[id]' and mem_id = '$user_id'";
			else //비회원(회원도 포함)인 경우 현재의 IP, connect time이 answer에 없으면 통과1임
				$query = "select * from answer where survey_id = '$row[id]' and ip = '$session_ip' and connect_time = '$session_connect_time'";
		}
		$result = mysqli_query($conn, $query);
		$result_rows = mysqli_num_rows($result);
		if($result_rows == 0)
			$condition_satisfy_isonce = 1;
		// --------------------------------------------------------------------------
		
		//5. 참여인원이 비어있는 경우
		// --------------------------------------------------------------------------
		$condition_satisfy_capacity = 0;
		if($row[max_capacity] == 0)	//참여가능인원이 무제한이므로 통과
			$condition_satisfy_capacity = 1;
		else { //참여가능인원이 유한하므로, 응답자수에 따라서 통과
			$query = "select * from answer where survey_id = '$row[id]'";
			$result = mysqli_query($conn, $query);
			$result_rows = mysqli_num_rows($result);
			if($row[max_capacity] > $result_rows)
				$condition_satisfy_capacity = 1;	//아직 여유있음
			else
				$condition_satisfy_capacity = 0;	//꽉 차서 더이상 설문필요없음
		}
		
		
		//6. $row[require_login] == 1인 경우, 현재로그인 상태인 경우
		// --------------------------------------------------------------------------
		$condition_satisfy_requirelogin = 0;
		if($row[require_login] == 0)	//로그인 요구하지 않는 설문의 경우 다 통과
			$condition_satisfy_requirelogin = 1;
		else	{ //로그인 요구되었을때, 
			if($is_login == 1) $condition_satisfy_requirelogin = 1;
			else $condition_satisfy_requirelogin = 0;
		}
	
		$survey = array('id' => intval($row['id']),
										'title' => $row['title'],
										'category' => $row['category'],
										'keywords' => $keyword_list,
										'is_once' => intval($row['is_once']),
										'start_date' => $row['start_date'],
										'end_date' => $row['end_date'],
										'start_time' => $row['start_time'],
										'end_time' => $row['end_time'],
										'rest_capacity' => intval($row['max_capacity'] - $numResults),
										'max_capacity' => intval($row['max_capacity']),
										'require_login' => intval($row['require_login']),
										'is_mid_result' => intval($row['mid_result']),
										'member_id' => $row['member_id'],
										'view_count' => intval($row['view_count']),
										'is_joinable' => ($condition_satisfy_location == 1 && $condition_satisfy_time == 1 && $condition_satisfy_basiccond == 1 && $condition_satisfy_isonce == 1 && $condition_satisfy_capacity == 1 && $condition_satisfy_requirelogin == 1)
										);
		array_push($survey_list, $survey);
	}
	echo json_encode(array('survey_list' => $survey_list, 'query' => array($survey_query)));
	//echo $location_id, $location_type;
?>